package com.lagou.app;


import com.lagou.entity.Dept;
import com.lagou.entity.Employee;
import com.lagou.utils.DateUtils;
import com.lagou.utils.DruidUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

public class testDBUtils {
    @Test
    public void Testinsert()  {
        QueryRunner qr=null;
        Connection con=null;
        try {
            qr = new QueryRunner();
//        String sql="insert jdbc_user values(?,?,?,?)";
//        Object[] param={null,"zhangfei",123,"1999-12-23"};
//        int i = qr.update(sql, param);
             con = DruidUtils.getConnection();
            con.setAutoCommit(false);
            String sql="select balance from account where card= ?";
            Double money=(double)qr.query(con,sql, new ScalarHandler<>(), "1122334455");

            if(money>5000){

                String sql1="UPDATE account SET balance=balance-? WHERE card=?";
                Object[] param={5000,"1122334455"};
                int i = qr.update(con,sql1,param);
               System.out.println(1/0);
                String sql2="update account set balance=balance+ ? where card= ? ";
                Object[] param2={5000,"5544332211"};
                int i1 = qr.update(con,sql2, param2);
                con.commit();
                System.out.println("转账成功");
                insert();

            }
            else {
                System.out.println("抱歉，你的余额不足！！！");
            }
        } catch (SQLException throwables) {
            try {
                con.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }finally {
            DbUtils.closeQuietly(con);
        }

    }
    public void insert() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql="insert transaction values(?,?,?,?,?)";
        Object[] param={null,"1122334455","转出",5000, DateUtils.getDateFormart()};
        qr.update(sql,param);
        QueryRunner qr1 = new QueryRunner(DruidUtils.getDataSource());
        String sql1="insert transaction values(?,?,?,?,?)";
        Object[] param1={null,"5544332211","转入",5000,DateUtils.getDateFormart()};
        qr.update(sql1,param1);
    }
    @Test
    // 查询价格高于2000元，生产日期是2019年之前的所有手机
    public void TestSelect() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql="SELECT pname FROM phone WHERE price>? AND prodate< ?";
        Object[] param={2000,"2019-01-01"};
        List<Object[]> query = queryRunner.query(sql, new ArrayListHandler(), param);
        for(Object[] objects:query){
            System.out.println(Arrays.toString(objects));
        }

    }
    @Test
    //查询所有颜色是白色的手机信息
    public void TestSelect2() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql="SELECT * FROM phone WHERE color=?";
        List<Object[]> query = qr.query(sql, new ArrayListHandler(), "白色");
        for(Object[] objects:query){
            System.out.println(Arrays.toString(objects));
        }

    }
    @Test
    //需求2: 查询每个员工的 姓名, 薪资 和 所属部门名称
    public void TestEmplyeeSelect() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql="select * from employee where did is not null ";
        List<Employee> list = qr.query(sql, new BeanListHandler<Employee>(Employee.class));
        String sql2="select * from dept where id=?";
        for(Employee employee:list){
            Dept dept = qr.query(sql2, new BeanHandler<Dept>(Dept.class),employee.getDid());
            employee.setDept(dept);
            System.out.println(employee.getNAME()+" "+employee.getSalary()+" "+employee.getDept().getDeptname());
        }

    }
    @Test
    //需求1: 查询所有的员工信息 (不包含没有部门的员工)。
    public void TestEmplyeeSelect2() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql="SELECT * FROM employee WHERE did IS NOT NULL";
        List<Employee> query = queryRunner.query(sql, new BeanListHandler<Employee>(Employee.class));
        for(Employee employee: query){
            System.out.println(employee);
        }

    }

}
